package br.com.naybee.framework.client.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import br.com.naybee.framework.client.connection.ConnectionDb;
import br.com.naybee.framework.client.freemarker.FreemarkerTemplate;
import br.com.naybee.framework.client.models.ListModel;
import br.com.naybee.framework.client.models.Model;
import br.com.naybee.framework.client.models.ModelError;
import br.com.naybee.framework.client.models.Models;
import br.com.naybee.framework.client.utils.NAYException;
import br.com.naybee.framework.client.utils.TypeColumm;

@Service
public class DaoImpl implements Dao {

	private Connection connection;
	private Statement statement;

	private Logger logger = LoggerFactory.getLogger(DaoImpl.class);

	private void conectando() throws SQLException{
		connection = ConnectionDb.conectando();
		statement = connection.createStatement();
	}

	public void add(String script, Model model) {
		try {
			conectando();
			statement.executeUpdate(fileParse(script, model));
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw new NAYException(ModelError.SQL_GENERIC, e.getMessage());
		} catch (NullPointerException e) {
			logger.error(e.getMessage());
			throw new NAYException(ModelError.SQL_GENERIC, e.getMessage());
		}
	}

	public void add(String script) {
		add(script, null);
	}

	public ListModel list(String script, Model model) {
		try {
			conectando();
			ResultSet resultSet = statement.executeQuery(fileParse(script, model));
			return ListModelData(resultSet, resultTypoColumm(resultSet.getMetaData()));
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw new NAYException(ModelError.SQL_GENERIC, e.getMessage());
		} catch (NullPointerException e) {
			logger.error(e.getMessage());
			throw new NAYException(ModelError.SQL_GENERIC, e.getMessage());
		}
	}

	public ListModel list(String script) {
		return list(script, Models.newModel());
	}

	public Model find(String script, Model model) {
		try {
			conectando();
			ResultSet resultSet = statement.executeQuery(fileParse(script, model));
			return modelData(resultSet, resultTypoColumm(resultSet.getMetaData()));
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw new NAYException(ModelError.SQL_GENERIC, e.getMessage());
		} catch (NullPointerException e) {
			logger.error(e.getMessage());
			throw new NAYException(ModelError.SQL_GENERIC, e.getMessage());
		}
	}

	public Model find(String script) {
		return find(script, Models.newModel());
	}

	private Model resultTypoColumm(ResultSetMetaData metaData) {
		Model model = new Model();
		try {
			for (int i = 1; i <= metaData.getColumnCount(); i++) {
				model.add(metaData.getColumnName(i), metaData.getColumnType(i));
			}
			return model;
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw new NAYException(ModelError.SQL_GENERIC, e.getMessage());
		}
	}

	private ListModel ListModelData(ResultSet resultSet, Model model) {

		Model modelKeys = Model.getKeys(model);
		Model modelValues = Model.getValues(model);
		ListModel listModel = new ListModel();
		try {
			while (resultSet.next()) {
				Model modelRegistro = new Model();
				for (int i = 0; i < model.size(); i++) {
					if (modelValues.getInt("values" + (i + 1)) == TypeColumm.VARCHAR.getType()) {
						modelRegistro.add(modelKeys.getStr("key" + (i + 1)), resultSet.getString(modelKeys.getStr("key" + (i + 1))));
					} else if (modelValues.getInt("values" + (i + 1)) == TypeColumm.INTEGER.getType()) {
						modelRegistro.add(modelKeys.getStr("key" + (i + 1)), resultSet.getInt(modelKeys.getStr("key" + (i + 1))));
					}
				}
				listModel.add(modelRegistro);
			}
			return listModel;
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw new NAYException(ModelError.SQL_GENERIC, e.getMessage());
		} finally {
			closeConnection();
		}
	}

	private Model modelData(ResultSet resultSet, Model model) {
		try {
			Model modelKeys = Model.getKeys(model);
			Model modelValues = Model.getValues(model);
			Model modelRegistro = new Model();
			while (resultSet.next()) {
				for (int i = 0; i < model.size(); i++) {
					if (modelValues.getInt("values" + (i + 1)) == TypeColumm.VARCHAR.getType()) {
						modelRegistro.add(modelKeys.getStr("key" + (i + 1)), resultSet.getString(modelKeys.getStr("key" + (i + 1))));
					} else if (modelValues.getInt("values" + (i + 1)) == TypeColumm.INTEGER.getType()) {
						modelRegistro.add(modelKeys.getStr("key" + (i + 1)), resultSet.getInt(modelKeys.getStr("key" + (i + 1))));
					}
				}
			}
			return modelRegistro;
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw new NAYException(ModelError.SQL_GENERIC, e.getMessage());
		} finally {
			closeConnection();
		}
	}

	private void closeConnection() {
		try {
			connection.close();
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw new NAYException(ModelError.SQL_GENERIC, e.getMessage());
		}
	}

	private String fileParse(String script, Model model) {
		return FreemarkerTemplate.parseFile(getClass(), script, model);
	}

}
